﻿using DBUtility;
using Jmit.Common;
using Jmit.Deyi.AutoTask;
using Quartz;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Xml;

namespace Jmit.Deyi.Job
{
    // 不允许此 Job 并发执行任务（禁止新开线程执行）
    // [DisallowConcurrentExecution]
    public class TimingJob : IJob
    {

        /// <summary>
        /// 定时任务：从orcale取数据同步到MySQL
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public Task Execute(IJobExecutionContext context)
        {
            return Task.Factory.StartNew(() =>
            {

                sync_data();
                sale_data();

            });
        }

        private void sync_data()
        {
            string sync_message = "";
            string sync_result = "fail";
            int sync_count = 0;
            string sale_count = "";

            string strCnn_from = AppConfigManager.LoadConfig().dbConn_from;
            string strCnn_to = AppConfigManager.LoadConfig().dbConn_to;

            try
            {
                Console.WriteLine(@" Hello Quartz.Net");
                LogBLL.log("开始");


                // orcale取数据
                //string sql_from = $" select {AppConfigManager.appConfig.fieldName} from {AppConfigManager.appConfig.userName}.\"{AppConfigManager.appConfig.tableName}\"   ";
                //string sql_from = $" select {AppConfigManager.appConfig.fieldName} from \"{AppConfigManager.appConfig.tableName}\"   ";
                //string sql_from = $" select  `artiid`, `artiname`, `artispec`, `manufactory`, `retailprice`  from  wxlink.\"jm\"      ";

                //string sql_from = $" select deptid as artiid, deptno as artiname, deptname as artispec,'' as manufactory,'' as retailprice from {AppConfigManager.appConfig.userName}.\"{AppConfigManager.appConfig.viewName}\"   ";
                //string sql_from = $" select `artiid`, `artiname`, `artispec`, `manufactory`, `retailprice` from {AppConfigManager.appConfig.userName}.\"{AppConfigManager.appConfig.viewName}\"   ";

                //string sql_from = $" select * from {AppConfigManager.appConfig.tableName} ";


                //查询主档数据
                string sql_from = $" select * from jm ";


                // 统计每日销售数据数量
                //object cnt = OracleHelper.GetSingle(strCnn_from, "select count(*) from JM1");
                //sale_count = cnt == null ? "" : cnt.ToString();

                DataSet ds = OracleHelper.Query(strCnn_from, sql_from);
                DataTable DT = ds.Tables[0];
                if (DT != null && DT.Rows.Count > 0)
                {
                    LogBLL.log("行数：" + DT.Rows.Count);

                    List<string> sqlList = new List<string>();


                    sqlList.Add($" truncate table deyi_zdxx ");
                    //MydbmanagerSql.ExecuteNonQuerySql(strCnn_to, $" truncate table deyi_zdxx ");


                    foreach (DataRow row in DT.Rows)
                    {

                        /*string deptid = row["deptid"].ToString();
                        string deptno = row["deptno"].ToString();
                        string deptname = row["deptname"].ToString();*/

                        string artiid = String2Json(row["artiid"].ToString());
                        string artiname = String2Json(row["artiname"].ToString());
                        string artispec = String2Json(row["artispec"].ToString());
                        string manufactory = String2Json(row["manufactory"].ToString());
                        string retailprice = String2Json(row["retailprice"].ToString());


                        //sqlList.Add($" REPLACE INTO `deyi_zdxx`(`artiid`, `artiname`, `artispec`, `manufactory`, `retailprice`) VALUES ('{deptid}', '{deptno}', '{deptname}','',''); ");
                        //sqlList.Add($" insert INTO `deyi_zdxx`(`artiid`, `artiname`, `artispec`, `manufactory`, `retailprice`) VALUES ('{artiid}', '{artiname}', '{artispec}','{manufactory}','{retailprice}'); ");

                        string sql = $" insert INTO `deyi_zdxx`(`artiid`, `artiname`, `artispec`, `manufactory`, `retailprice`) VALUES ('{artiid}', '{artiname}', '{artispec}','{manufactory}','{retailprice}') ";
                        //LogBLL.log("artiid：" + artiid);
                        //LogBLL.log("sql：" + sql);

                        sqlList.Add(sql);
                        //MydbmanagerSql.ExecuteNonQuerySql(strCnn_to, sql);
                        //LogBLL.log("artiid：" + artiid + " 完成");
                    }

                    //sqlList.Add("call test_log()");

                    LogBLL.log("SQL执行开始");
                    sqlList.Add("call editGoodsKey()");
                    MyOpDB.SaveDataToDB(strCnn_to, sqlList);

                    //MydbmanagerSql.ExecuteNonQuerySql(strCnn_to, $" call editGoodsKey() ");

                    LogBLL.log("SQL执行结束");






                    sync_message = "导入完成";
                    sync_result = "success";
                    sync_count = DT.Rows.Count;
                }
            }
            catch (Exception ex)
            {
                sync_message = "导入失败，错误消息=" + ex.Message;
                sync_result = "fail";
                LogBLL.Err(sync_message);
            }
            finally
            {
                // 记录日志
                MydbmanagerSql.ExecuteNonQuerySql(strCnn_to, $"insert into deyi_zdxx_log (sync_message,sync_result,sync_count,made_time) VALUES ('{sync_message}', '{sync_result}', '{sync_count}',now()) ");

                // 统计当天销售数据
                // MydbmanagerSql.ExecuteNonQuerySql(strCnn_to, $"insert into deyi_zdxx_log (sync_message,sync_result,sync_count,made_time) VALUES ('今日销售数据数量：{sale_count}条', '{sync_result}', '{sale_count}',now()) ");
                


            }
        }

        private void sale_data()
        {

            string sync_message = "";
            string sync_result = "fail";
            int sync_count = 0;
            string sale_count = "";


            string strCnn_from = AppConfigManager.LoadConfig().dbConn_from;
            string strCnn_to = AppConfigManager.LoadConfig().dbConn_to;

            try
            {



                // 当天销售数据
                string sql_from = $@" select * from JM1 order by trancountid   ";
                string asyn_time = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");

                DataSet ds = OracleHelper.Query(strCnn_from, sql_from);
                DataTable DT = ds.Tables[0];
                if (DT != null && DT.Rows.Count > 0)
                {
                    //LogBLL.log("数据：" + DT.Rows.Count + "条");

                    List<string> sqlList = new List<string>();


                    //sqlList.Add($" truncate table deyi_zdxx ");
                    //MydbmanagerSql.ExecuteNonQuerySql(strCnn_to, $" truncate table deyi_zdxx ");


                    foreach (DataRow row in DT.Rows)
                    {

                        /*string deptid = row["deptid"].ToString();
                        string deptno = row["deptno"].ToString();
                        string deptname = row["deptname"].ToString();*/

                        string deptid = String2Json(row["deptid"].ToString());
                        string deptname = String2Json(row["deptname"].ToString());
                        string tranid = String2Json(row["tranid"].ToString());
                        string trancountid = String2Json(row["trancountid"].ToString());
                        string artiid = String2Json(row["artiid"].ToString());
                        string artiname = String2Json(row["artiname"].ToString());
                        string artispec = String2Json(row["artispec"].ToString());
                        string manufactory = String2Json(row["manufactory"].ToString());
                        string saleprice = String2Json(row["saleprice"].ToString());
                        string qty = String2Json(row["qty"].ToString());
                        string amt = String2Json(row["amt"].ToString());
                        string trandate = String2Json(row["trandate"].ToString());



                        string sql = $" insert INTO `deyi_sale_total`(`deptid`,`deptname`,`tranid`,`trancountid`,`artiid`, `artiname`, `artispec`, `manufactory`, `saleprice`, `qty`, `amt`, `trandate`, `asyn_time`, `made_time`) VALUES ('{deptid}','{deptname}','{tranid}','{trancountid}','{artiid}', '{artiname}', '{artispec}','{manufactory}','{saleprice}','{qty}','{amt}','{trandate}','{asyn_time}',now()) ";
                        //LogBLL.log("sql：" + sql);

                        sqlList.Add(sql);

                        //MydbmanagerSql.ExecuteNonQuerySql(strCnn_to, sql);
                        //LogBLL.log("artiid：" + artiid + " 完成");
                    }


                    bool isok = MyOpDB.SaveDataToDB(strCnn_to, sqlList);
                    //LogBLL.log("导入完成isok：" + isok + "");

                    //MydbmanagerSql.ExecuteNonQuerySql(strCnn_to, $" call editGoodsKey() ");


                    sync_message = "导入完成";
                    sync_result = "success";
                    sync_count = DT.Rows.Count;
                    sale_count = DT.Rows.Count.ToString();


                }
            }
            catch (Exception ex)
            {

            }
            finally
            {

                // 统计当天销售数据
                MydbmanagerSql.ExecuteNonQuerySql(strCnn_to, $"insert into deyi_zdxx_log (sync_message,sync_result,sync_count,made_time) VALUES ('今日销售数据数量：{sale_count}条', '{sync_result}', '{sale_count}',now()) ");

            }

        }



        /// <summary>
        /// 过滤特殊字符
        /// </summary>
        /// <param name="s">字符串</param>
        /// <returns>json字符串</returns>
        private static string String2Json(String s)
        {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < s.Length; i++)
            {
                char c = s.ToCharArray()[i];
                switch (c)
                {
                    case '\'':
                        sb.Append("\\\'"); break;
                    case '\"':
                        sb.Append("\\\""); break;
                    case '\\':
                        sb.Append("\\\\"); break;
                    case '/':
                        sb.Append("\\/"); break;
                    case '\b':
                        sb.Append("\\b"); break;
                    case '\f':
                        sb.Append("\\f"); break;
                    case '\n':
                        sb.Append("\\n"); break;
                    case '\r':
                        sb.Append("\\r"); break;
                    case '\t':
                        sb.Append("\\t"); break;
                    default:
                        sb.Append(c); break;
                }
            }
            return sb.ToString();
        } 

    }
}
